﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace BackOffice
{
    public class Maintenance
    {
        public static DataTable listMember(string name, string email, string date, int role, int status)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listMember";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 100));
                cmd.Parameters["@Email"].Value = email;

                cmd.Parameters.Add(new SqlParameter("@RegisterDate", SqlDbType.VarChar, 50));
                cmd.Parameters["@RegisterDate"].Value = date;

                cmd.Parameters.Add(new SqlParameter("@Role", SqlDbType.Int));
                cmd.Parameters["@Role"].Value = role;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable getRewardPromotion()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listRewardCategory";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable getActivedPromotion()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listActivatedRewardPromotion";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable getSelectedPromotion(int promotion)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listRewardPromotion";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@PromotionID", SqlDbType.Int));
                cmd.Parameters["@PromotionID"].Value = promotion;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static void UpdateRewardPromotion(int promoID, double rate, double addOn, string promotion, int stat)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "UpdateRewardPromotion";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@PromotionID", SqlDbType.Int));
                cmd.Parameters["@PromotionID"].Value = promoID;

                cmd.Parameters.Add(new SqlParameter("@Rate", SqlDbType.Decimal));
                cmd.Parameters["@Rate"].Value = rate;

                cmd.Parameters.Add(new SqlParameter("@Plus", SqlDbType.Decimal));
                cmd.Parameters["@Plus"].Value = addOn;

                cmd.Parameters.Add(new SqlParameter("@Promotion", SqlDbType.VarChar, 50));
                cmd.Parameters["@Promotion"].Value = promotion;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = stat;

                cmd.ExecuteNonQuery();
                
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }
        }

        public static void AddRewardPromotion(double rate, double addOn, string promotion, int stat)
        {
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "AddRewardPromotion";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Rate", SqlDbType.Decimal));
                cmd.Parameters["@Rate"].Value = rate;

                cmd.Parameters.Add(new SqlParameter("@Plus", SqlDbType.Decimal));
                cmd.Parameters["@Plus"].Value = addOn;

                cmd.Parameters.Add(new SqlParameter("@Promotion", SqlDbType.VarChar, 50));
                cmd.Parameters["@Promotion"].Value = promotion;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = stat;

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }
        }

        public static int addRewardProduct(string prodName, int point, int Quantity, int Status,  string imagePath)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "AddRewardProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProdName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProdName"].Value = prodName;

                cmd.Parameters.Add(new SqlParameter("@Point", SqlDbType.Int));
                cmd.Parameters["@Point"].Value = point;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = Quantity;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = Status;

                cmd.Parameters.Add(new SqlParameter("@ImagePath", SqlDbType.VarChar, 100));
                cmd.Parameters["@ImagePath"].Value = imagePath;

                result = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static DataTable listRewardProduct(string prodName, int pointAbove, int pointBelow, int QuantityBelow, int status)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            conndb db = new conndb();

            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listRewardProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProdName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProdName"].Value = prodName;

                cmd.Parameters.Add(new SqlParameter("@PointAbove", SqlDbType.Int));
                cmd.Parameters["@PointAbove"].Value = pointAbove;

                cmd.Parameters.Add(new SqlParameter("@PointBelow", SqlDbType.Int));
                cmd.Parameters["@PointBelow"].Value = pointBelow;

                cmd.Parameters.Add(new SqlParameter("@QuantityBelow", SqlDbType.Int));
                cmd.Parameters["@QuantityBelow"].Value = QuantityBelow;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;

                da.SelectCommand = cmd;
                da.Fill(dt);
                
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable showRewardProduct(int ID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "showRewardProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int checkRewardNameRedundant(string ProdName)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();
            SqlDataReader dtr;

            try
            {
                cmd.CommandText = "checkRewardNameRedundant";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProdName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProdName"].Value = ProdName;

                dtr = cmd.ExecuteReader();

                if(dtr.HasRows)
                    result++;
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static DataTable checkRewardNameUpdated(int id, string prodName)
        {           
            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            try
            {
                cmd.CommandText = "checkRewardProductName";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = id;

                cmd.Parameters.Add(new SqlParameter("@ProdName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProdName"].Value = prodName;

                da.SelectCommand = cmd;
                da.Fill(dt);
                
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int updateRewardProduct(int id, string prodName, int point, int quantity, int status)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "updateRewardProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = id;

                cmd.Parameters.Add(new SqlParameter("@ProdName", SqlDbType.VarChar, 50));
                cmd.Parameters["@ProdName"].Value = prodName;

                cmd.Parameters.Add(new SqlParameter("@Point", SqlDbType.Int));
                cmd.Parameters["@Point"].Value = point;

                cmd.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int));
                cmd.Parameters["@Quantity"].Value = quantity;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;

                result = cmd.ExecuteNonQuery();                

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static DataTable listAdvertisement(int status)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();
            
            try
            {
                cmd.CommandText = "listAdvertisement";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
                cmd.Parameters["@Status"].Value = status;
                
                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable listAdvertisement2(int ID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();
            
            try
            {
                cmd.CommandText = "listAdvertisement2";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;
                
                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static int approvedAdvertisement(int ID, string name, int period)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "approvedAdvertisement";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
                cmd.Parameters["@Name"].Value = name;

                cmd.Parameters.Add(new SqlParameter("@Period", SqlDbType.Int));
                cmd.Parameters["@Period"].Value = period;

                result = cmd.ExecuteNonQuery();

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static int cancelAdvertisement(int ID)
        {
            int result = 0;

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "cancelAdvertisement";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
                cmd.Parameters["@ID"].Value = ID;

                result = cmd.ExecuteNonQuery();

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return result;
        }

        public static DataTable listAdvertisementPeriod()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listAdvertisementPeriod";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable listAdvertisementPictureSize()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listAdvertisementPictureSize";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);

            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;

        }
    }    
}
